﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using System.Data.SqlClient;

namespace PuroGame.Database
{
    public class Access
    {
        private static string _CONN_STRING = string.Empty;

        public static string CONN_STRING
        {
            get
            {
                if (!string.IsNullOrEmpty(_CONN_STRING))
                    return _CONN_STRING;
                else
                    return "CONN_STRING";
            }
            set
            {
                _CONN_STRING = value;
            }
        }

        #region Internal Methods
        
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static IDbConnection OpenConnection()
        {
            IDbConnection connection = null;
            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[CONN_STRING].ConnectionString;

            try
            {
                connection = new SqlConnection(connectionString);
                connection.Open();
                return connection;
            }
            catch (Exception ex)
            {
                connection.Close();
                connection.Dispose();

                throw new Exception("Ops, não foi possivel conectar ao banco de dados." + ex.ToString());
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DynamicParameters SetDynamicParameters(Dictionary<string, object> param)
        {
            DynamicParameters dynamicParameters = new DynamicParameters();

            foreach (KeyValuePair<string, object> keyPair in param)
            {
                dynamicParameters.Add(keyPair.Key, keyPair.Value);
            }
            return dynamicParameters;
        }

        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pQuery"></param>
        /// <param name="param"></param>
        /// <param name="pCommandType"></param>
        /// <returns></returns>
        /// 
        public static IEnumerable<T> Get<T>(string pQuery, Dictionary<string, object> param) where T : class
        {
            return Get<T>(pQuery, param, CommandType.Text);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pQuery"></param>
        /// <param name="param"></param>
        /// <param name="pCommandType"></param>
        /// <returns></returns>
        public static IEnumerable<T> Get<T>(string pQuery, Dictionary<string, object> param, System.Data.CommandType pCommandType) where T : class
        {
            DynamicParameters dynamicParameters = new DynamicParameters();

            if (param.Count > 0)
                dynamicParameters = SetDynamicParameters(param);

            using (IDbConnection connection = OpenConnection())
            {
                return connection.Query<T>(pQuery, dynamicParameters, commandType: pCommandType);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <typeparam name="T"></typeparam>
        /// <param name="pQuery"></param>
        /// <param name="Map"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static IEnumerable<T> Get<T1, T2, T>(string pQuery, Func<T1,T2,T> Map, Dictionary<string, object> param) where T : class
        {
            DynamicParameters dynamicParameters = new DynamicParameters();
            IEnumerable<T> list;
            
            if(param.Count > 0)
                dynamicParameters = SetDynamicParameters(param);
            
            using (IDbConnection connection = OpenConnection())
            {
                list = connection.Query<T1, T2, T>(pQuery, Map, param: dynamicParameters);
            }

            return list.Distinct();
        }

        /// <summary>
        /// Esta chamada é util, quando for preciso carregar os dados de um elemento no formato 1-N, por exemplo, carregar uma lista de ItemData, na propriedade
        /// ItemData (List<ListItemData>) do Item.
        /// </summary>
        /// <typeparam name="T">Tipo do elemento principal</typeparam>
        /// <typeparam name="T1">Tipo do elemento secundário</typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static KeyValuePair<dynamic, dynamic> GetMultiple<T, T1>(string query, Dictionary<string, object> param) 
        {
            KeyValuePair<dynamic, dynamic> keyValue;
            
            using (IDbConnection conn = OpenConnection())
            {
                using (var multi = conn.QueryMultiple(query, SetDynamicParameters(param)))
                {
                    var single = multi.Read<T>().Single();
                    var list = multi.Read<T1>().ToList();
                    keyValue = new KeyValuePair<dynamic, dynamic>(single, list);
                }
            }

            return keyValue;
        }
    }
}

